package boogui.money;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import boogui.member.MemberVO;
import boogui.movie.MovieAreaVO;
import boogui.movie.MovieVO;
import boogui.seat.MemberSeat4;

import oracle.jdbc.proxy.annotation.Pre;


public class reservationDAO implements IresurvationDAO{
	
	private reservationVO rvo = new reservationVO();
	
	
	Scanner sc = new Scanner(System.in);
	private final String DRV = "oracle.jdbc.driver.OracleDriver";
	private final String URL = "jdbc:oracle:thin:jwj8508/jwj8508@112.216.253.125:1521:xe";
	private String insertSQL = "insert into reservation values(movie.nextval, ?, ?, ?, ?, ?, ?)";
	private String selectSQL = "select m.memname, mv.title, cn.cinename, r.people, r.price" +
								" from reservation r inner join logininfo l"+
								" on r.memno = l.memno"+
								" inner join member m"+
								" on m.memno = r.memno"+
								" inner join movies mv"+
								" on mv.movno = r.movno"+
								" inner join cinema cn"+
								" on cn.cineno = r.cineno order by reserveno";
	private String deleteSQL = "delete reservation where memno = ?, reserveno = ?";
	
	
	
	
	@Override
	public boolean insertVation(reservationVO rvo) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(insertSQL);
			pstmt.setString(1, rvo.getMemno());
			pstmt.setString(2, rvo.getMovno());
			pstmt.setString(3, rvo.getCineno());
			pstmt.setInt(4, rvo.getPeople());
			pstmt.setInt(5, rvo.getSeat());
			pstmt.setInt(6, rvo.getPrice());
			pstmt.executeUpdate();
			result = true;
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}

	@Override
	public List<reservationVO> selectVation() {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<reservationVO> result = new ArrayList<>();
		
		try{
			conn = openDatabase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			
			//로그인 한상태에서 memno 랑 로그인한 memno 랑 비교하는걸 몰라서 주석만 쳐놓을게요
			reservationVO rvo = new reservationVO();
			MemberVO mvo = new MemberVO();
			MovieVO mvvo = new MovieVO();
			MovieAreaVO mavo = new MovieAreaVO();
			
			while(rs.next()){
			
			mvo.setMemname(rs.getString("memname"));
			mvvo.setMovtitle(rs.getString("title"));
			mavo.setCinename(rs.getString("cinename"));
			rvo.setPeople(rs.getInt("people"));
			rvo.setPrice(rs.getInt("price"));
			
			result.add(rvo);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}
	
	@Override
	public boolean deleteVation(reservationVO rvo) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(deleteSQL);
			pstmt.setString(1, rvo.getMemno());
			
			
			result = true;
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}
	
	@Override
	public Connection openDatabase() {
		
		try{
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		}catch (Exception ex) {
			ex.printStackTrace();
		}
		
		return null;
	}

	@Override
	public void closeDatabase(Connection c, PreparedStatement p, ResultSet r) {
		
		if(c != null)try{c.close(); c = null;}catch (Exception ex) {}
		if(p != null)try{p.close(); p = null;}catch (Exception ex) {}
		if(r != null)try{r.close(); r = null;}catch (Exception ex) {}
		
		
	}

	
}
